
-- ============
-- comma separated example
DECLARE @data1 TABLE (
	record_id INT NOT NULL
	, string VARCHAR(MAX) NULL
);
DECLARE @separator1 VARCHAR(64) = ',';

INSERT INTO @data1 (
	record_id
	, string
)
SELECT *
FROM (
	VALUES (1, 'abc,def,ghi')
		, (2, ',jkl,mno,pqr,')
		, (3, NULL)
		, (4, '')
		, (5, ',')
		, (6, '"hello, world",hello\, world') -- quoted or escaped strings are not handled
) a (b, c)
;

SELECT *
FROM @data1
CROSS APPLY ssf.utl_string_split(string, @separator1) -- CROSS APPLY like JOIN
ORDER BY record_id
	, string_order
OPTION (MAXRECURSION 0) -- 0 allows unlimited recursion, usually not necessary
;
-- ============

-- ============
-- space separated example
DECLARE @data2 TABLE (
	record_id INT NOT NULL
	, string VARCHAR(MAX) NULL
);
DECLARE @separator2 VARCHAR(64) = SPACE(1);

INSERT INTO @data2 (
	record_id
	, string
)
SELECT *
FROM (
	VALUES (1, 'A space separated example.')
		, (2, 'One space.  Two spaces.')
		, (3, NULL)
		, (4, '')
		, (5, ' ')
) a (b, c)
;

SELECT *
FROM @data2
OUTER APPLY ssf.utl_string_split(string, @separator2) -- OUTER APPLY like LEFT JOIN
ORDER BY record_id
	, string_order
OPTION (MAXRECURSION 0)
;
-- ============
